import org.junit.Test;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Main {

    public static void main(String[] args) {
      /*  try
        {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名lincj.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:lincj.db");
            //Connection conn = DriverManager.getConnection("jdbc:sqlite:path(路径)/lincj.db");
            Statement stat = conn.createStatement();
            stat.executeUpdate( "create table table1(name varchar(64), age int);" );//创建一个表，两列
            stat.executeUpdate( "insert into table1 values('aa',12);" ); //插入数据
            stat.executeUpdate( "insert into table1 values('bb',13);" );
            stat.executeUpdate( "insert into table1 values('cc',20);" );

            ResultSet rs = stat.executeQuery("select * from table1;"); //查询数据

            while (rs.next()) { //将查询到的数据打印出来

                System.out.print("name = " + rs.getString("name") + " "); //列属性一
                System.out.println("age = " + rs.getString("age")); //列属性二
            }
            rs.close();
            conn.close(); //结束数据库的连接
        }
        catch( Exception e )
        {
            e.printStackTrace ( );
        }*/
    }

    /**
     * 插入用户信息
     * */
    @Test
    public void insertUser(){
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名lincj.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:djd.db");
            //Connection conn = DriverManager.getConnection("jdbc:sqlite:path(路径)/lincj.db");

            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate("CREATE TABLE T_SYS_USER (JH TEXT,XM TEXT,DWDM TEXT,STATUS TEXT,GXSJ TEXT);");

            List<List<String>> result = ReadExcel.parseExcel("user.xls");
            for (int i = 1; i < result.size(); i++) {
                List<String> temp = result.get(i);
//                System.out.println(temp);
                String template = "insert into T_SYS_USER values('%s','%s','%s','%s','%s');";
//                String template = "insert into T_SYS_USER(JH,XM,DWDM,STATUS,GXSJ) values('%s','%s','%s','%s','%s');";
                String sql = String.format(template,temp.get(1),temp.get(2),temp.get(3),"0","2017/5/27 18:00:50");
//                System.out.println(sql);
                stat.executeUpdate(sql); //插入数据
            }

            conn.commit();
//            ResultSet rs = stat.executeQuery("select * from table1;"); //查询数据


      /*      while (rs.next()) { //将查询到的数据打印出来

                System.out.print("name = " + rs.getString("name") + " "); //列属性一
                System.out.println("age = " + rs.getString("age")); //列属性二
            }*/
//            rs.close();
            conn.close(); //结束数据库的连接
        }
        catch( Exception e ) {
            e.printStackTrace ( );
        }
    }


    /**
     * 插入单位 数据
     */
//    CREATE TABLE "T_XT_ZZJGB" ("BH" TEXT,"MC" TEXT,"STATUS" TEXT,"GXSJ" TEXT);
    @Test
    public void insertxt(){
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名lincj.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:djd.db");
            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate( "CREATE TABLE T_XT_ZZJGB (BH TEXT,MC TEXT,STATUS TEXT,GXSJ TEXT);");

            List<List<String>> result = ReadExcel.parseExcel("zzjgb.xls");
            for (int i = 1; i < result.size(); i++) {
                List<String> temp = result.get(i);
                String template = "insert into T_XT_ZZJGB values('%s','%s','%s','%s');";
                String sql = String.format(template,temp.get(1),temp.get(2),"0","2017/5/31 18:00:50");
                stat.executeUpdate(sql); //插入数据
            }
            conn.commit();
            conn.close(); //结束数据库的连接
        }
        catch( Exception e ) {
            e.printStackTrace ( );
        }
    }


    /**
     * 插入问题分类表
     * fltwbh   法律条文编号
     * subclass 法律条文
     * ajbm     上级编号
     * dj       等级
     */
    @Test
    public void insertClassify() throws IOException, ClassNotFoundException, SQLException {
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名lincj.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:djd.db");
            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate( "CREATE TABLE wtfl(fltwbh Text PRIMARY KEY , mc Text, sjbm Text, dj Text);");

            List<List<String>> result = ReadExcel.parseExcel("wtfl.xls");
            System.out.println("行数："+result.size());

            for (int i = 1; i < result.size(); i++) {
                List<String> temp = result.get(i);
                String template = "insert into wtfl values('%s','%s','%s','%s');";
                String sql = String.format(template,temp.get(1), temp.get(2),temp.get(3),temp.get(4));
                stat.executeUpdate(sql);
            }


            conn.commit();
            conn.close(); //结束数据库的连接
        }
        catch( Exception e ) {
            e.printStackTrace ( );
        }
    }
    @Test
    public void insertClassifyOld(){
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名lincj.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:djd.db");
            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate( "CREATE TABLE classify(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    " broadname Text, classifynm Text, subclass Text, sjbm Text, fltwbh Text);");

            List<List<String>> result = ReadExcel.parseExcel("wtfl.xls");
            List<ClassifyData> listUnit = new ArrayList<ClassifyData>();

            for (int i = 1; i < result.size(); i++) {
                List<String> temp = result.get(i);
                ClassifyData bean = new ClassifyData(temp.get(4),temp.get(2),temp.get(1),temp.get(3));
                listUnit.add(bean);
            }
            List<ClassifyNewData> listclassifynewdata = new ArrayList<ClassifyNewData>();

            for (ClassifyData classifydata : listUnit) {
                if (classifydata.dj.equals("1")) {
                    ClassifyNewData classifynewdata = new ClassifyNewData();
                    classifynewdata.broadname = classifydata.mc;
                    classifynewdata.fltwbh = classifydata.fltwbh;
                    listclassifynewdata.add(classifynewdata);
                } else if (classifydata.dj.equals("2")) {
                    ClassifyNewData classifynewdata = new ClassifyNewData();
                    classifynewdata.classifynm = classifydata.mc;
                    classifynewdata.sjbm = classifydata.sjbm;
                    classifynewdata.fltwbh = classifydata.fltwbh;
                    listclassifynewdata.add(classifynewdata);
                } else if (classifydata.dj.equals("3")) {
                    ClassifyNewData classifynewdata = new ClassifyNewData();
                    classifynewdata.subclass = classifydata.mc;
                    classifynewdata.sjbm = classifydata.sjbm;
                    classifynewdata.fltwbh = classifydata.fltwbh;
                    listclassifynewdata.add(classifynewdata);
                }

            }
            for (ClassifyNewData person : listclassifynewdata) {
           /*     stat.executeUpdate("insert into classify(broadname, classifynm, subclass,sjbm,fltwbh) values(?,?,?,?,?)",
                        new String[] { person.broadname, person.classifynm, person.subclass, person.sjbm, person.fltwbh
                        });*/
                String template = "insert into classify(broadname, classifynm, subclass,sjbm,fltwbh) values('%s','%s','%s','%s','%s');";
                String sql = String.format(template,person.broadname, person.classifynm, person.subclass, person.sjbm, person.fltwbh);
                stat.executeUpdate(sql);
            }
           /* String template = "insert into classify values('%s','%s','%s','%s');";
            String sql = String.format(template,temp.get(1),temp.get(2),temp.get(3),temp.get(4));
            stat.executeUpdate(sql); //插入数据
*/
            conn.commit();
            conn.close(); //结束数据库的连接
        }
        catch( Exception e ) {
            e.printStackTrace ( );
        }
    }

}
